package com.yanyilu.qiubite.jpa;

import com.yanyilu.qiubite.entity.AdminUser;
import com.yanyilu.qiubite.entity.Spot;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface AdminUserJpa extends JpaRepository<AdminUser,Integer>, JpaSpecificationExecutor<AdminUser> {

    @Query(value = "select a.id,a.user_name,a.role,a.spot_id,s.spot_name from admin_user a left join spot s on s.spot_id = a.spot_id where a.spot_id = :spotId",nativeQuery = true)
    Page<Object[]> findBySpotId(@Param("spotId")Integer spotId, Pageable pageable);


    @Query(value = "select count(*) from admin_user where user_name = :userName and id <> :userId",nativeQuery = true)
    int countByUserName(@Param("userName") String userName,@Param("userId")Integer userId);

    @Query(value = "select spot_id from admin_user where id = :userId",nativeQuery = true)
    Integer findSpotIdByUserId(@Param("userId")Integer userid);

    @Query(value = "select s.* from spot s left join admin_user a on s.spot_id = a.spot_id where a.id = :userId",nativeQuery = true)
    Spot findSpotByUserId(@Param("userId") Integer userid);

    @Query(value = "select count (id) from admin_user where user_name = :userName",nativeQuery = true)
    Integer countByUserName(@Param("userName") String userName);

    @Query(value = "select a.id,a.user_name,a.role,a.spot_id,s.spot_name from admin_user a left join spot s on s.spot_id = a.spot_id",nativeQuery = true)
    Page<Object[]> findAllUser(Pageable pageable);

    @Modifying
    @Query(value = "delete from admin_user where spot_id = :spotId",nativeQuery = true)
    void deleteBySpotId(@Param("spotId")Integer spotId);
}
